const pool = require('../models/db');

// 获取库存信息
const getStocks = async (query) => {
  const { goods_id, goods_name, warning_threshold, stock_status, remarks } = query;
  let sqlQuery = 'SELECT s.*, g.goods_name FROM stock s JOIN goods g ON s.goods_id = g.goods_id';
  let conditions = [];
  let values = [];

  if (goods_id) {
    conditions.push('s.goods_id = $' + (conditions.length + 1));
    values.push(goods_id);
  }
  if (goods_name) {
    conditions.push('g.goods_name ILIKE $' + (conditions.length + 1));
    values.push(`%${goods_name}%`);
  }
  if (warning_threshold) {
    conditions.push('s.warning_threshold = $' + (conditions.length + 1));
    values.push(warning_threshold);
  }
  if (stock_status) {
    conditions.push('s.stock_status = $' + (conditions.length + 1));
    values.push(stock_status);
  }
  if (remarks) {
    conditions.push('s.remarks ILIKE $' + (conditions.length + 1));
    values.push(`%${remarks}%`);
  }

  if (conditions.length > 0) {
    sqlQuery += ' WHERE ' + conditions.join(' AND ');
  }
  const results = await pool.query(sqlQuery, values);
  return { items: results.rows, total: results.rowCount };
};

// 新建库存信息
const addStock = async (data) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { goods_name, warning_threshold, stock_quantity, remarks } = data;
    const goodsResult = await client.query('SELECT goods_id FROM goods WHERE goods_name = $1', [goods_name]);
    if (goodsResult.rowCount === 0) {
      throw new Error('No goods found with this name');
    }
    const goods_id = goodsResult.rows[0].goods_id;

    let stock_status = '充足';
    if (stock_quantity <= warning_threshold) {
      stock_status = stock_quantity === 0 ? '缺货' : '紧张';
    }

    const results = await client.query(
      'INSERT INTO stock (goods_id, warning_threshold, stock_quantity, stock_status, remarks) VALUES ($1, $2, $3, $4, $5) RETURNING *',
      [goods_id, warning_threshold, stock_quantity, stock_status, remarks || null]
    );
    await client.query('COMMIT');
    return results.rows[0];
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

// 更新库存信息
const updateStock = async (id, data) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { goods_name, warning_threshold, stock_quantity, remarks } = data;
    const goodsResult = await client.query('SELECT goods_id FROM goods WHERE goods_name = $1', [goods_name]);
    if (goodsResult.rowCount === 0) {
      throw new Error('No goods found with this name');
    }
    const goods_id = goodsResult.rows[0].goods_id;

    let stock_status = '充足';
    if (stock_quantity <= warning_threshold) {
      stock_status = stock_quantity === 0 ? '缺货' : '紧张';
    }

    const results = await client.query(
      'UPDATE stock SET warning_threshold = $1, stock_quantity = $2, stock_status = $3, remarks = $4 WHERE goods_id = $5 RETURNING *',
      [warning_threshold, stock_quantity, stock_status, remarks || null, goods_id]
    );
    await client.query('COMMIT');
    return results.rows[0];
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

// 更新库存信息
const updateStockByTrans = async (client, goods_id, quantityChange) => {
    let stock = await client.query('SELECT * FROM stock WHERE goods_id = $1 FOR UPDATE', [goods_id]);
    if (stock.rowCount === 0) {
      await client.query(
        'INSERT INTO stock (goods_id, stock_quantity, warning_threshold, stock_status) VALUES ($1, $2, $3, $4)',
        [goods_id, 0, 0, '缺货']
      );
      stock = await client.query('SELECT * FROM stock WHERE goods_id = $1 FOR UPDATE', [goods_id]);
    }
    const newQuantity = stock.rows[0].stock_quantity + quantityChange;
    if (newQuantity < 0) {
      throw new Error('库存数量不能小于0');
    }
    let stockStatus = '充足';
    if (newQuantity <= stock.rows[0].warning_threshold) {
      stockStatus = newQuantity === 0 ? '缺货' : '紧张';
    }
    await client.query(
      'UPDATE stock SET stock_quantity = $1, stock_status = $2 WHERE goods_id = $3',
      [newQuantity, stockStatus, goods_id]
    );
  };

// 删除库存信息
const deleteStock = async (id) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const results = await client.query('DELETE FROM stock WHERE goods_id = $1 RETURNING *', [id]);
    await client.query('COMMIT');
    if (results.rowCount === 0) {
      throw new Error('No stock found with this ID');
    }
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

// 获取库存信息建议
const getSuggestions = async (query) => {
  const { field, query: searchString } = query;
  let results;
  if (field === 'goods_name') {
    results = await pool.query(
      `SELECT DISTINCT ${field} FROM goods WHERE ${field} ILIKE $1`,
      [`%${searchString}%`]
    );
  } else {
    results = await pool.query(
      `SELECT DISTINCT ${field} FROM stock WHERE ${field} ILIKE $1`,
      [`%${searchString}%`]
    );
  }
  return results.rows.map(row => row[field]);
};

module.exports = {
  getStocks,
  addStock,
  updateStock,
  updateStockByTrans,
  deleteStock,
  getSuggestions,
};
